package com.etc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.etc.dao.ProductDao;
import com.etc.pojo.Product;
import com.etc.utils.DBUtil;

public class ProductDaoImpl implements ProductDao {

	@Override
	public List<Product> query() {
		List<Product> list = new ArrayList<Product>();
		ResultSet resultSet = DBUtil.doQuery("select * from product");
		try {
			while (resultSet.next()) {
				int productId = resultSet.getInt("product_id"); // user_id 参数是数据库表中的列名
				String productName = resultSet.getString("product_name"); // user_id 参数是数据库表中的列名
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				list.add(new Product(productId, productName, price, count));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Product queryById(Integer productId) {
		Product product = null;
		ResultSet resultSet = DBUtil.doQuery("select * from product where product_id = ?",productId );
		try {
			while (resultSet.next()) {
				String productName = resultSet.getString("product_name"); 
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				product = new Product(productId, productName, price, count);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}

	@Override
	public int add(Product product) {
		// TODO Auto-generated method stub
		return DBUtil.doUpdate("insert into product values(null,?,?,?)", 
				product.getProductName(), product.getPrice(), product.getCount());
	}

	@Override
	public int updateById(Product product) {
		return DBUtil.doUpdate("update product set product_name = ?, price = ?, count =  ? where product_id = ?", 
				product.getProductName(), product.getPrice(), product.getCount(), product.getProductId());
	}

	@Override
	public int deleteById(Integer productId) {
		return DBUtil.doUpdate("delete from product where product_id = ?", productId);
	}

	@Override
	public List<Product> queryLikeName(String productNameKey) {
		List<Product> list = new ArrayList<Product>();
		ResultSet resultSet = DBUtil.doQuery("select * from product where product_name like ?", "%" + productNameKey +"%");
		try {
			while (resultSet.next()) {
				int productId = resultSet.getInt("product_id"); // user_id 参数是数据库表中的列名
				String productName = resultSet.getString("product_name"); // user_id 参数是数据库表中的列名
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				list.add(new Product(productId, productName, price, count));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int count() {
		int count  = 0;
		ResultSet resultSet = DBUtil.doQuery("select count(*) from product");
		try {
			while (resultSet.next()) {
				// count = resultSet.getInt("count(*)");  // 参数是列名
				count = resultSet.getInt(1); // 参数列的索引
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<Product> orderByCountDesc() {
		List<Product> list = new ArrayList<Product>();
		ResultSet resultSet = DBUtil.doQuery("select * from product order by count desc");
		try {
			while (resultSet.next()) {
				int productId = resultSet.getInt("product_id"); // user_id 参数是数据库表中的列名
				String productName = resultSet.getString("product_name"); // user_id 参数是数据库表中的列名
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				list.add(new Product(productId, productName, price, count));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Product> orderByPriceDesc() {
		List<Product> list = new ArrayList<Product>();
		ResultSet resultSet = DBUtil.doQuery("select * from product order by price desc");
		try {
			while (resultSet.next()) {
				int productId = resultSet.getInt("product_id"); // user_id 参数是数据库表中的列名
				String productName = resultSet.getString("product_name"); // user_id 参数是数据库表中的列名
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				list.add(new Product(productId, productName, price, count));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Product> orderByColumn(String column) {
		List<Product> list = new ArrayList<Product>();
		String sql = "";
		if ("price".equals(column)) {
			sql = "select * from product order by price desc";
		} else if ("count".equals(column)){
			sql = "select * from product order by count desc";
		}
		ResultSet resultSet = DBUtil.doQuery(sql);
		try {
			while (resultSet.next()) {
				int productId = resultSet.getInt("product_id"); // user_id 参数是数据库表中的列名
				String productName = resultSet.getString("product_name"); // user_id 参数是数据库表中的列名
				float price = resultSet.getFloat("price");
				int count = resultSet.getInt("count");
				list.add(new Product(productId, productName, price, count));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
